﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DBUtilitySqlsever
{
    public abstract class PageHelper
    {
        public static DataSet GetPagedData(int pageIndex, int pageSize, string commandText, string keyId, out int totalRecordCount, out int totalPageCount)
        {
            DataSet ds = new DataSet();
            totalRecordCount = 0;
            totalPageCount = 0;

            StringBuilder countCommandText = new StringBuilder();
            countCommandText.Append(" select count(1) from ( ");
            countCommandText.Append(commandText);
            countCommandText.Append(" ) as t ");

            try
            {
                totalRecordCount = Convert.ToInt32(DbHelperSQL.QueryTable(countCommandText.ToString()).Rows[0][0].ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            if (totalRecordCount % pageSize == 0)
            {
                totalPageCount = totalRecordCount / pageSize;
            }
            else
            {
                totalPageCount = totalRecordCount / pageSize + 1;
            }

            int pageStart = 0;
            int pageEnd = 0;

            pageStart = (pageIndex - 1) * pageSize + 1;
            pageEnd = pageIndex * pageSize;

            StringBuilder rowCommandText = new StringBuilder();
            rowCommandText.Append("#" + commandText.ToLower().TrimStart());
            rowCommandText.Replace("#select", "select  row_number() over (order by " + keyId + ") as rowNum,");

            StringBuilder pageCommandText = new StringBuilder();
            pageCommandText.Append(" select * from ( ");
            pageCommandText.Append(rowCommandText);
            pageCommandText.Append(" ) as t ");
            pageCommandText.Append(" where rowNum between " + pageStart + " and " + pageEnd + " ");

            try
            {
                ds = DbHelperSQL.Query(pageCommandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
            return ds;
        }
    }
}
